<?php

namespace XWAM\Addon\PXCallSystem\Controller;

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of XToolController
 *
 * @author Alen
 */
class XToolController extends AppbaseController {

    //put your code here
    public function index() {
        
    }

    public function data_clear() {
        $action = I('action');
        $date = I('date');
        $siteID = I('siteID');
        $search['date'] = $date;
        $search['siteID'] = I('siteID');
        $str_search = $siteID ? ' and siteID=' . $siteID : '';
        $str_date = $date ? "'$date'" : '';
        if ($action && !$date) {
            $this->error('未选择日期','',1);
        }
        $cdrtable = 'db_xcall_cdr_history.cdr_' . $siteID;
        $cdr=sofn_get_sharding_field('DB_CDR').'cdr';
        $M = M();
        if ($action == 'search') {
            if ($date == date('Y-m-d')) {
                $data['nRecord_today'] = M($cdr)->where("date_format(start,'%%Y-%%m-%%d') <= " . $str_date . ' and recordingfile <> \'\'' . $str_search)->count();
                $data['nCallHistory_today'] = M($cdr)->where("date_format(start,'%%Y-%%m-%%d') <= " . $str_date . $str_search)->count();
                $data['nCallHistory_other'] = M($cdr)->where("date_format(start,'%%Y-%%m-%%d') <= " . $str_date . " and siteID is null")->count();
                $data['nRecord'] = 0;
                $data['nCallHistory'] = 0;
            } else {
                $data['nRecord_today'] = 0;
                $data['nCallHistory_today'] = 0;
                $data['nCallHistory_other'] = 0;
                if (I('siteID')) {
                    $data['nRecord'] = M($cdrtable)->where("date_format(start,'%%Y-%%m-%%d') <= " . $str_date . ' and recordingfile <> \'\'' . $str_search)->count();
                    $data['nCallHistory'] = M($cdrtable)->where("date_format(start,'%%Y-%%m-%%d') <= " . $str_date . $str_search)->count();
                } else {
                    $data['nRecord'] = '未选择租户';
                    $data['nCallHistory'] = '未选择租户';
                }
            }
            $data['nseat_call_stat'] = M('tstat_seat_call')->where('billdate <= ' . $str_date . $str_search)->count();
            $data['nextension_call_stat'] = M('tstat_extension_call')->where('billdate <= ' . $str_date . $str_search)->count();
            $data['nseat_work_stat'] = M('tstat_seat_work')->where('billdate <= ' . $str_date . $str_search)->count();
            $data['nseat_calltime_stat'] = M('tstat_extension_work')->where('billdate <= ' . $str_date . $str_search)->count();
            $data['nqueue_calltime_stat'] = M('tstat_queue_work')->where('billdate <= ' . $str_date . $str_search)->count();
            $data['nsmshistory'] = M('tsms_queue')->where("date_format(createdTime,'%%Y-%%m-%%d') <= " . $str_date . $str_search)->count();
            foreach ($data as &$item) {
                $item = "($item)";
            }
            $this->assign('data', $data);
        } else if ($action == 'clear') {
            $result = true;
            if ($date == date('Y-m-d')) {
                if (I('callhistory_all_today') == '1' && $result !== false) {
                    $list = M($cdr)->where("date_format(start,'%%Y-%%m-%%d') <= " . $str_date . $str_search)->field('recordingfile')->select();
                    if ($list) {
                        foreach ($list as $row) {
                            unlink('/var/spool/asterisk/monitor/' . parse_voc_path($row['recordingfile']));
                        }
                        unset($list);
                    }
                    $result = M($cdr)->where("date_format(start,'%%Y-%%m-%%d') <= " . $str_date . $str_search)->delete();
                }
                if (I('callhistory_rec_today') == '1' && $result !== false) {
                    $list = M($cdr)->where("date_format(start,'%%Y-%%m-%%d') <= " . $str_date . ' and recordingfile <> \'\'' . $str_search)->field('recordingfile')->select();
                    if ($list) {
                        foreach ($list as $row) {
                            unlink('/var/spool/asterisk/monitor/' . parse_voc_path($row['recordingfile']));
                        }
                        unset($list);
                    }
                }
                if (I('call_history_other') == '1' && $result !== false) {
                    $result = M($cdr)->where("date_format(start,'%%Y-%%m-%%d') <= " . $str_date . " and  siteID is null")->delete();
                }
            } else {
                if (I('callhistory_all') == '1' && $result !== false) {
                    $list = M($cdrtable)->where("date_format(start,'%%Y-%%m-%%d') <= " . $str_date . $str_search)->field('recordingfile')->select();
                    if ($list) {
                        foreach ($list as $row) {
                            unlink('/var/spool/asterisk/monitor/' . parse_voc_path($row['recordingfile']));
                        }
                        unset($list);
                    }
                    $result = M($cdrtable)->where("date_format(start,'%%Y-%%m-%%d') <= " . $str_date . $str_search)->delete();
                }
                if (I('callhistory_rec') == '1' && $result !== false) {
                    $list = M($cdrtable)->where("date_format(start,'%%Y-%%m-%%d') <= " . $str_date . ' and recordingfile <> \'\'' . $str_search)->field('recordingfile')->select();
                    if ($list) {
                        foreach ($list as $row) {
                            unlink('/var/spool/asterisk/monitor/' . parse_voc_path($row['recordingfile']));
                        }
                        unset($list);
                    }
                }
            }
            if (I('seat_call_stat') == '1' && $result !== false) {
                $result = M('tstat_seat_call')->where('billdate <= ' . $str_date . $str_search)->delete();
            }
            if (I('extension_call_stat') == '1' && $result !== false) {
                $result = M('tstat_extension_call')->where('billdate <= ' . $str_date . $str_search)->delete();
            }
            if (I('seat_work_stat') == '1' && $result !== false) {
                $result = M('tstat_seat_work')->where('billdate <= ' . $str_date . $str_search)->delete();
            }
            if (I('seat_calltime_stat') == '1' && $result !== false) {
                $result = M('tstat_extension_work')->where('billdate <= ' . $str_date . $str_search)->delete();
            }
            if (I('queue_calltime_stat') == '1' && $result !== false) {
                $result = M('tstat_queue_work')->where('billdate <= ' . $str_date . $str_search)->delete();
            }
            if (I('smshistory') == '1' && $result !== false) {
                $result = M('tsms_queue')->where("date_format(createdTime,'%%Y-%%m-%%d') <= " . $str_date . $str_search)->delete();
            }
            if ($result === false) {
                $this->error('执行出错','',1);
            } else {
                $this->success('执行成功', AddonU('XTool/data_clear'));
            }
            exit;
        }
        $this->assign('search', $search);
        $this->display();
    }

    public function data_backup() {
        $this->display();
    }

    public function get_databackup_list_json() {
        $where['exTags'] = 'backup_db';
        $where['siteID'] = $this->loginData['siteID'];
        $M = M('tgeneral_file');
        $page = I('page', 1) . ',' . I('rows', C('USER.PAGE_SIZE'));
        $total = $M->cache(true)->where($where)->count('id');
        $list = $M->cache(true)->where($where)->field('id, fileName, filePath, fileSize, createdTime')->order('id desc')->page($page)->select();
        $out = array();
        foreach ($list as $row) {
            $row['fileSizeText'] = format_bytes($row['fileSize']);
            $row['siteIDText'] = get_siteID_text($row['siteID']);
            $out[] = $row;
        }
        $this->ajaxReturn(array('total' => $total, 'rows' => $out));
    }

    /**
     * 备份数据或结构
     * @since 1.0 2014-9-26 by sutroon
     */
    public function data_backup_save() {
        $dir = UPLOAD_ROOT . 'Backup/';
        if (!is_dir($dir) && !mkdir($dir, 0777, true)) {
            $this->ajaxMsg(false, '创建文件夹失败!');
        }
        //$dir = str_replace('/', '\\', $dir);
        // read data
        $database = C('DB_NAME');
        $str = '/**' . PHP_EOL;
        $str .= ' * database: ' . $database . PHP_EOL;
        $str .= ' * created time: ' . date('Y-m-d H:i:s') . PHP_EOL;
        $str .= ' * author: sutroon' . PHP_EOL;
        $str .= ' */' . PHP_EOL;
        if (I('type') == 'schema') {
            $result = mysql_query("select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, `ENGINE`, TABLE_ROWS, DATA_LENGTH, AUTO_INCREMENT, CREATE_TIME, `TABLE_COLLATION`, TABLE_COMMENT  from  information_schema.TABLES where TABLE_TYPE='BASE TABLE' and TABLE_SCHEMA='$database';");
            while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                $str .= $this->get_create_object_sql($row['TABLE_NAME'], 'TABLE') . ';' . PHP_EOL . PHP_EOL;
            }
            $result = mysql_query("select TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION from information_schema.VIEWS where TABLE_SCHEMA='$database';");
            while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                $str .= preg_replace('/DEFINER=[^\s]+ /', '', $this->get_create_object_sql($row['TABLE_NAME'], 'VIEW')) . ';' . PHP_EOL . PHP_EOL;
            }
            $result = mysql_query("show procedure status;");
            while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                if ($row['Db'] == $database) {
                    $str .= preg_replace('/DEFINER=[^\s]+ /', '', $this->get_create_object_sql($row['Name'], 'PROCEDURE')) . ';' . PHP_EOL . PHP_EOL;
                }
            }
            $result = mysql_query("show function status;");
            while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
                if ($row['Db'] == $database) {
                    $str .= preg_replace('/DEFINER=[^\s]+ /', '', $this->get_create_object_sql($row['Name'], 'FUNCTION')) . ';' . PHP_EOL . PHP_EOL;
                }
            }
            $filename = 'schema-' . date('Y-m-d-his') . '.sql';
        } else {
            $result = mysql_query('show tables;');
            while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
                $str .= $this->backup_table_data($row[0]);
            }
            $filename = 'data-' . date('Y-m-d-his') . '.sql';
        }
        $size = file_put_contents($dir . $filename, $str);
        if (false === $size) {
            $this->ajaxMsg(false, '保存文件失败!');
        }
        $data['fileName'] = $filename;
        $data['filePath'] = __ROOT__ . '/Uploads/Backup/' . $filename;
        $data['fileSize'] = $size;
        $data['fileType'] = '';
        $data['fileExt'] = '.sql';
        $data['exTags'] = 'backup_db';
        $data['createdTime'] = date('Y-m-d H:i:s');
        $data['siteID'] = $this->loginData['siteID'];
        $result = M('tgeneral_file')->add($data);
        $this->ajaxMsg(true, '数据备份成功!');
    }

    /**
     * 显示创建对象的语句
     * @param $object
     * @param string $type
     * @return string
     * @since 1.0 2014-9-26 by sutroon
     */
    function get_create_object_sql($object, $type = 'TABLE') {
        switch (strtolower($type)) {
            case 'view':
                $script = 'Create View';
                break;
            case 'procedure':
                $script = 'Create Procedure';
                break;
            case 'function':
                $script = 'Create Function';
                break;
            default:
                $script = 'Create Table';
                break;
        }
        $result = mysql_query("Show $script $object;");
        $row = mysql_fetch_array($result, MYSQLI_ASSOC);
        if (!isset($row[$script])) {
            die($object . '::' . $type);
        }
        return $result ? $row[$script] : '';
    }

    /**
     * 备份单个数据表的用户数据
     * @param $table
     * @return string
     * @since 1.0 2014-9-26 by sutroon
     */
    function backup_table_data($table) {
        $str = '';
        $result = mysql_query("select * from `$table`");
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $keys = array_keys($row);
            $keys = array_map('addslashes', $keys);
            $keys = join('`,`', $keys);
            $keys = "`" . $keys . "`";
            $vals = array_values($row);
            $vals = array_map('addslashes', $vals);
            $vals = join("','", $vals);
            $vals = "'" . $vals . "'";
            $str .= "insert into `$table` ($keys) values ($vals);" . PHP_EOL;
        }
        return $str;
    }

    public function data_recovery_save() {
        $id = I('id');
        if (!$id)
            $this->ajaxMsg(false, '编号有误!');
        $M = M('tgeneral_file');
        $path = $M->where('id=' . $id)->getField('filePath');
        if (!$path) {
            $this->ajaxMsg(false, '文件不存在!');
        }
        if (!file_exists($path)) {
            $this->ajaxMsg(false, '服务器上不存在备份文件!');
        }
        mysql_connect(C('DB_HOST'), C('DB_USER'), C('DB_PWD')) or die('连接数据库失败!');
        mysql_select_db(C('DB_NAME')) or die('数据库不存在!');
        $sql_value = "";
        $cg = 0;
        $sb = 0;
        $sqls = file($path);
        foreach ($sqls as $sql) {
            $sql_value .= $sql;
        }
        $a = explode(";\r\n", $sql_value); //根据";\r\n"条件对数据库中分条执行
        $total = count($a) - 1;
        var_dump($a);
        die($total);
        mysql_query("set names 'utf8'");
        for ($i = 0; $i < $total; $i++) {
            mysql_query("set names 'utf8'");
            //执行命令
            if (mysql_query($a[$i])) {
                $cg += 1;
            } else {
                $sb += 1;
                $sb_command[$sb] = $a[$i];
            }
        }
        $msg = "操作完毕，共处理 $total 条命令，成功 $cg 条，失败 $sb 条";
        //显示错误信息
        if ($sb > 0) {
            $msg .= "<div style='font-size:12px; line-height:14px; font-weight:normal;'><strong style='display:block;padding:5px 0px;'>失败命令如下：</strong>";
            for ($ii = 1; $ii <= $sb; $ii++) {
                $msg .= "第 " . $ii . " 条命令（内容如下）：<br>" . $sb_command[$ii] . "<br>";
            }
            $msg .= '</div>';
        }
        $this->redirect(AddonU('XTool/data_backup'), null, 10, $msg);
    }

    public function data_delete_save($id = '') {
        if (!$id) {
            $this->ajaxMsg(false, '编号有误!');
        }
        $M = M('tgeneral_file');
        $paths = $M->where(array('id' => array('in', $id)))->getField('filePath', true);
        $result = $M->delete($id);
        if ($result === false) {
            $this->ajaxMsg(false, '删除失败!');
        } else {
            foreach ($paths as $path) {
                unlink($_SERVER['DOCUMENT_ROOT'] . $path);
            }
            $this->ajaxMsg(true, '删除成功!');
        }
    }

}
